--------------------------------------------------------------------------------
-- Sites Table
--------------------------------------------------------------------------------
CREATE TABLE RA$Sites (
  siteName          VARCHAR2(100),
  isSecurityEnabled CHAR(1) CONSTRAINT sites_securityEnabled_rqd NOT NULL,
  CONSTRAINT RA$Sites_pk PRIMARY KEY (siteName),
  CONSTRAINT sites_securityEnabled_yn_ck CHECK (isSecurityEnabled IN ('Y','N'))
);

--------------------------------------------------------------------------------
-- Permission Sets Table
--------------------------------------------------------------------------------
CREATE TABLE RA$PermissionSets (
  permissionSetID NUMBER(10),
  siteName        CONSTRAINT permSets_siteName_rqd NOT NULL,
  CONSTRAINT RA$permissionSets_pk PRIMARY KEY (permissionSetID),
  CONSTRAINT permSets_siteName_fk FOREIGN KEY (siteName)
    REFERENCES RA$Sites(siteName)
);

CREATE INDEX RA$PermSets_siteName_fidx
ON RA$PermissionSets(siteName);

CREATE SEQUENCE RA$PermissionSetID_Seq
MINVALUE 1 MAXVALUE 9999999999
NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER RA$PermissionSets_ins_trg
BEFORE INSERT ON RA$PermissionSets
FOR EACH ROW
WHEN (new.permissionSetID IS NULL)
BEGIN
  :new.permissionSetID := RA$PermissionSetID_Seq.NEXTVAL;
END;
/

--------------------------------------------------------------------------------
-- Permissions Table
--------------------------------------------------------------------------------
CREATE TABLE RA$Permissions (
  permissionSetID  ,
  roleName         VARCHAR2(200),
  viewPage         CHAR(1) DEFAULT 'N' CONSTRAINT perms_viewPage_rqd NOT NULL,
  editPage         CHAR(1) DEFAULT 'N' CONSTRAINT perms_editPage_rqd NOT NULL,
  editPermissions  CHAR(1) DEFAULT 'N' CONSTRAINT perms_editPermissions_rqd NOT NULL,
  createPage       CHAR(1) DEFAULT 'N' CONSTRAINT perms_createPage_rqd NOT NULL,
  createFolder     CHAR(1) DEFAULT 'N' CONSTRAINT perms_createFolder_rqd NOT NULL,
  manageRevisions  CHAR(1) DEFAULT 'N' CONSTRAINT perms_manageRevisions_rqd NOT NULL,
  managePublishing CHAR(1) DEFAULT 'N' CONSTRAINT perms_managePublishing_rqd NOT NULL,
  CONSTRAINT RA$permissions_pk PRIMARY KEY (permissionSetID, roleName),
  CONSTRAINT perms_permissionSetID_fk FOREIGN KEY (permissionSetID)
    REFERENCES RA$PermissionSets(permissionSetID) ON DELETE CASCADE,
  CONSTRAINT perms_viewPage_yn_ck CHECK (viewPage IN ('Y','N')),
  CONSTRAINT perms_editPage_yn_ck CHECK (editPage IN ('Y','N')),
  CONSTRAINT perms_editPermissions_yn_ck CHECK (editPermissions IN ('Y','N')),
  CONSTRAINT perms_createPage_yn_ck CHECK (createPage IN ('Y','N')),
  CONSTRAINT perms_createFolder_yn_ck CHECK (createFolder IN ('Y','N')),
  CONSTRAINT perms_manageRevisions_yn_ck CHECK (manageRevisions IN ('Y','N')),
  CONSTRAINT perms_managePublishing_yn_ck CHECK (managePublishing IN ('Y','N'))
);

--------------------------------------------------------------------------------
-- Folders Table
--------------------------------------------------------------------------------
CREATE TABLE RA$Folders (
  folderID        NUMBER(10),
  siteName        CONSTRAINT fldrs_site_rqd NOT NULL,
  folderName      AS (NVL(SUBSTR(RTRIM(path, '/'), INSTR(RTRIM(path, '/'), '/', -1) + 1), 'Site Root')),
  parentFolderID  ,
  path            VARCHAR2(4000) CONSTRAINT fldrs_path_rqd NOT NULL,
  permissionSetID ,
  CONSTRAINT RA$folders_pk PRIMARY KEY (folderID),
  CONSTRAINT fldrs_siteName_fk FOREIGN KEY (siteName)
    REFERENCES RA$Sites(siteName),
  CONSTRAINT fldrs_parentFolderID_fk FOREIGN KEY (parentFolderID)
    REFERENCES RA$Folders(folderID),
  CONSTRAINT fldrs_path_ending_slash_ck CHECK (SUBSTR(path, -1) = '/'),
  CONSTRAINT fldrs_path_begin_slash_ck CHECK (SUBSTR(path, 1, 1) = '/'),
  CONSTRAINT fldrs_permissionSetID_fk FOREIGN KEY (permissionSetID)
    REFERENCES RA$PermissionSets(permissionSetID)
);

-- Adjust the column size to avoid index creation failures
-- See http://readyapps.codeplex.com/workitem/575 for more details
DECLARE
  v_BlockSize NUMBER;
BEGIN
  SELECT t.block_size
    INTO v_BlockSize
    FROM user_users u
    JOIN user_tablespaces t
      ON t.tablespace_name = u.default_tablespace;
  
  IF v_BlockSize < 8192 THEN -- Less than 8K block size
    EXECUTE IMMEDIATE 'ALTER TABLE RA$Folders DROP COLUMN folderName';
    EXECUTE IMMEDIATE 'ALTER TABLE RA$Folders MODIFY path VARCHAR2(3000)';
    EXECUTE IMMEDIATE 'ALTER TABLE RA$Folders ADD folderName AS (NVL(SUBSTR(RTRIM(path, ''/''), INSTR(RTRIM(path, ''/''), ''/'', -1) + 1), ''Site Root''))';
    dbms_output.put_line('RA$Folders path column adjusted.');
  END IF;
END;
/

CREATE INDEX RA$Folders_parentFolderID_fidx
ON RA$Folders(parentFolderID);

CREATE INDEX RA$Folders_path_idx
ON RA$Folders(path);

CREATE INDEX RA$Folders_folderID_path_idx
ON RA$Folders(folderID, path);

CREATE UNIQUE INDEX RA$Folders_site_path_ci_uidx
ON RA$Folders(siteName, UPPER(path));

CREATE INDEX RA$Fldrs_permissionSetID_fidx
ON RA$Folders(permissionSetID);

CREATE SEQUENCE RA$FolderID_seq
MINVALUE 1 MAXVALUE 9999999999
START WITH 1 INCREMENT BY 1
NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER RA$Folders_ins_trg
BEFORE INSERT ON RA$Folders
FOR EACH ROW
WHEN (new.folderID IS NULL)
BEGIN
  :new.folderID := RA$FolderID_seq.NEXTVAL;
END;
/

--------------------------------------------------------------------------------
-- Pages Table
--------------------------------------------------------------------------------
CREATE TABLE RA$Pages (
  pageID           NUMBER(10),
  folderID         NUMBER(10) CONSTRAINT pages_folderID_rqd NOT NULL,
  pageName         VARCHAR2(1000) CONSTRAINT pages_pageName_rqd NOT NULL,
  currentRevision  NUMBER(8) CONSTRAINT pages_currentRevision_rqd NOT NULL,
  pendingRevision  NUMBER(8) CONSTRAINT pages_pendingRevision_rqd NOT NULL,
  publishable      VARCHAR2(1) DEFAULT 'N' CONSTRAINT pages_publishable_rqd NOT NULL,
  permissionSetID  ,
  retentionUnit    VARCHAR2(8),
  retentionMeasure NUMBER(3),
  CONSTRAINT RA$pages_pk PRIMARY KEY(pageID),
  CONSTRAINT pages_folderID_fk FOREIGN KEY(folderID)
    REFERENCES RA$Folders(folderID),
  CONSTRAINT pages_publishable_ck CHECK (publishable IN ('Y','N')),
  CONSTRAINT pages_permissionSetID_fk FOREIGN KEY (permissionSetID)
    REFERENCES RA$PermissionSets(permissionSetID) ON DELETE SET NULL,
  CONSTRAINT pages_retentionUnit_ck CHECK (retentionUnit IN ('Versions','Years','Months','Days','Forever'))
);

CREATE UNIQUE INDEX RA$Pages_folder_page_ci_uidx
ON RA$Pages(folderID, UPPER(pageName));

CREATE INDEX RA$Pages_permissionSetID_fidx
ON RA$Pages(permissionSetID);

CREATE SEQUENCE RA$PageID_seq
MINVALUE 1 MAXVALUE 9999999999
START WITH 1 INCREMENT BY 1
NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER RA$Pages_ins_trg
BEFORE INSERT ON RA$Pages
FOR EACH ROW
WHEN (new.pageID IS NULL)
BEGIN
  :new.pageID := RA$PageID_seq.NEXTVAL;
END;
/

--------------------------------------------------------------------------------
-- PageRevisions Table
--------------------------------------------------------------------------------
CREATE TABLE RA$PageRevisions (
  pageID         ,
  revisionNumber NUMBER(8),
  revisionStatus VARCHAR2(11) CONSTRAINT pageRev_revisionStatus_rqd NOT NULL,
  revisionDate   DATE DEFAULT sysdate CONSTRAINT pageRev_revisionDate_rqd NOT NULL,
  revisionAuthor VARCHAR2(100) CONSTRAINT pageRev_revisionAuthor_rqd NOT NULL,
  pageTitle      VARCHAR2(500) CONSTRAINT pageRev_pageTitle_rqd NOT NULL,
  pageContent    CLOB CONSTRAINT pageRev_pageContent_rqd NOT NULL,
  CONSTRAINT RA$pageRevisions_pk PRIMARY KEY(pageID, revisionNumber),
  CONSTRAINT pageRevisions_pageID_fk FOREIGN KEY(pageID)
    REFERENCES RA$Pages(pageID) ON DELETE CASCADE,
  CONSTRAINT pageRev_revisionStatus_ck CHECK (
    revisionStatus IN ('Current','Past','Pending'))
);

--------------------------------------------------------------------------------
-- PageLinks Table
--------------------------------------------------------------------------------
CREATE TABLE RA$PageLinks (
  pageID         ,
  revisionNumber ,
  pageLinkKey    NUMBER(4),
  displayText    VARCHAR2(1000) CONSTRAINT pageLinks_displayText_rqd NOT NULL,
  linkPageID     CONSTRAINT pageLinks_linkPageID_rqd NOT NULL,
  target         VARCHAR2(10),
  CONSTRAINT RA$pageLinks_pk PRIMARY KEY(pageID, revisionNumber, pageLinkKey),
  CONSTRAINT pageLinks_pageID_fk FOREIGN KEY(pageID, revisionNumber)
    REFERENCES RA$PageRevisions(pageID, revisionNumber) ON DELETE CASCADE,
  CONSTRAINT pageLinks_linkPageID_fk FOREIGN KEY(linkPageID)
    REFERENCES RA$Pages(pageID)
);

CREATE INDEX RA$PageLinks_linkPageID_fidx
ON RA$PageLinks(linkPageID);
